package com.tmpt.service.impl;

import com.googlecode.genericdao.search.Search;
import com.googlecode.genericdao.search.SearchResult;
import com.inspiresoftware.lib.dto.geda.adapter.BeanFactory;
import com.inspiresoftware.lib.dto.geda.assembler.DTOAssembler;
import com.tmpt.beans.*;
import com.tmpt.dao.impl.TemplateUtils;
import com.tmpt.others.pageUtils.PaginationService;
import com.tmpt.service.IMenuService;
import com.tmpt.service.ITemplateService;
import com.tmpt.service.utils.Column_Constant;
import com.tmpt.service.utils.SpringContextHolder;
import com.tmpt.dao.intf.*;
import com.tmpt.dto.*;
import com.tmpt.utils.Config;
import com.tmpt.utils.FenAndYuan;
import com.tmpt.utils.JsonMap;
import com.tmpt.utils.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.SQLException;
import java.util.*;

@Service
@Transactional(propagation = Propagation.REQUIRED,rollbackFor = Exception.class)
public class TemplateService implements ITemplateService {



	private Logger log = Logger.getLogger(this.getClass());

	//@Resource private ITemplateDao templateBizDao;
	//@Resource private ITemplateDao templateTemlDao;
	@Resource private IA1TemplateTableDao a1TemplateTableDao;
	@Resource private IA1TemplateColumnDao a1TemplateColumnDao;
	@Resource private IA1TemplateConditionDao a1TemplateConditionDao;
	@Resource private IA1TemplateConditionMultiQueryDao a1TemplateConditionMultiQueryDao;
	@Resource private IA1TemplateRightMenuDao a1TemplateRightMenuDao;
	@Resource private IA2Menu1Dao menu1Dao;
	@Resource private IA2Menu2Dao menu2Dao;
	@Resource private IA2MenuFunctionDao menuFunctionDao;
	@Resource private IA1TemplateColumnOptionDao optionDao;
	@Resource private IA1TemplateColumnOptionDataDao optionDataDao;
	@Resource private IA1TemplateColumnOptionHidColDao optionHidColDao;

	@Resource private ITemplateDao templateTemlDao;
	@Resource private TemplateServiceUtils templateServiceUtils;
	@Resource private TemplateUtils templateUtils;
	@Resource IA2UserDao a2UserDao;
	@Resource IA3MenuDao a3MenuDao;
	@Resource IA3MenuMyDao a3MenuMyDao;
	@Resource DataLimitService dataLimitService;

	//Map<String,String[]> paramMap = new HashMap<String, String[]>();
	//json:{"sortColumn":["id"],"keyRandom":["20150423144439356"],"balanceDate_Start":["2015-05-12"],"page":["1"],"sortOrder":["desc"],"balanceDate":["2015-05-04"],"rows":["20"]}
	//JSONObject jo = JSONObject.fromObject(paramJson);


	@Override
	public Result genSql4DynamicDS(String paramJson) throws Exception {

		Result resultObj = new Result();
		Map<String, Object> resultMap = null;
		try{
			Map<String,String[]> paramMap = JsonMap.json2Map(paramJson);
			//
			String keyRandom = paramMap.get("_keyRandom")[0];
			//查询历史数据
			String _historyId = null;
			if(paramMap.get("_historyId")!=null){
				_historyId = paramMap.get("_historyId")[0];
			}

			Search search = new Search();
			search.addFilterEqual("keyRandom", keyRandom);
			A1TemplateTable templateTable = (A1TemplateTable)a1TemplateTableDao.searchUnique(search);

			//加载数据源
			String dataSource = templateTable.getDataSource();
			ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);

			int mode = templateTable.getMode();
			//if(mode==0 || mode==1 || mode==3)
			{
				//SQL拼接,调用自定义SQL Table Name
				resultObj = templateDao.queryColumnAliasAndProperty(templateTable);
				Map<String,String> columnAliasMap
						= (Map<String,String>)resultObj.getResultMap().get("columnAliasMap");
				Map<String,Map<String,String>> dbcolumnsProperty
						= (Map<String,Map<String,String>>)resultObj.getResultMap().get("dbcolumnsProperty");

				//列所属表名
				Result result1 = templateUtils.queryRalationTableList(templateTable);
				Map<String,String> columnTableMap = (Map<String,String>)result1.getResultMap().get("columnTableMap");
				resultObj.getResultMap().put("columnTableMap",columnTableMap);
			}

			/*if(mode==2){
				//调用自定义SQL
				resultObj = templateDao.templateSearchBySql(templateTable,paramMap);
			}
			if(mode==3){
				//调用spring bean 来查询数据
				resultObj = invokeBean(beanObject,beanMethod,paramJson,_historyId,beanHistoryMethod);
			}
			if(mode==4){ //存储过程
				resultObj = templateDao.templateSearchByProcedure(templateTable, paramMap);
			}*/
			return resultObj;

		}catch (Exception e) {
			e.printStackTrace();
			return resultObj.fault(e.getLocalizedMessage());
		}

	}

	/**
	 *
	 * @param paramJson
	 * @param paramMap 做数据边界权限时新加的参数，如：userId,menu2Id。。。
	 * @return
	 */
	@Override
	public Result paginationBySearch(String paramJson,Map<String,Object> paramMap) {
		Result resultObj = new Result();
		Map<String, Object> resultMap = null;
		try{
			Map<String,String[]> paramMap1 = JsonMap.json2Map(paramJson);
			//
			String keyRandom = paramMap1.get("_keyRandom")[0];
			//查询历史数据
			String _historyId = null;
			if(paramMap1.get("_historyId")!=null){
				_historyId = paramMap1.get("_historyId")[0];
			}

			Search search = new Search();
			search.addFilterEqual("keyRandom", keyRandom);
			A1TemplateTable templateTable = (A1TemplateTable)a1TemplateTableDao.searchUnique(search);

			resultObj = paginationBySearch4DynamicDS(templateTable, paramMap1, paramJson, _historyId,paramMap);

			return resultObj;
			
		}catch (Exception e) {
			e.printStackTrace();
			return resultObj.fault(e.getLocalizedMessage());
		}
		
	}

	/**
	 * 事务关闭，通过a1TemplateTable来确认，取哪个DB
	 * @param templateTable
	 * @param paramMap
	 * @param paramJson
	 * @param _historyId
	 * @param paramMap 做数据边界权限时新加的参数，如：userId,menu2Id。。。
	 * @return
	 * @throws Exception
	 */
	@Override
	public Result paginationBySearch4DynamicDS(A1TemplateTable templateTable,
											   Map<String,String[]> paramMap1, String paramJson,
											   String _historyId,Map<String,Object> paramMap) throws Exception{
		Result resultObj = new Result();
		//加载数据源
		String dataSource = templateTable.getDataSource();
		ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);
		String beanObject = templateTable.getBeanObject();
		String beanMethod = templateTable.getBeanMethod();
		String beanHistoryMethod = templateTable.getBeanHistoryMethod();

		//数据边界控制
		//取当前用户访问的二级菜单的最高权限数据边界值
		Map<String,Object> paramMapDataLimit = dataLimitService.getLimitParam(paramMap);
		String dataLimitType = String.valueOf(paramMapDataLimit.get("dataLimitType"));
		String userId = String.valueOf(paramMapDataLimit.get("userId"));
		String companyId = String.valueOf(paramMapDataLimit.get("companyId"));
		String[] params = {dataLimitType,userId,companyId};

		int mode = templateTable.getMode();
		if(mode==0){
			//SQL拼接
			resultObj = templateDao.templateSearch(templateTable,paramMap1,params);
		}
		if(mode==1){
			//调用自定义SQL Table Name
			resultObj = templateDao.templateSearchByTableNameSql(templateTable,paramMap1,params);
		}
		if(mode==2){
			//调用自定义SQL
			resultObj = templateDao.templateSearchBySql(templateTable,paramMap1);
		}
		if(mode==3){
			//调用spring bean 来查询数据
			resultObj = invokeBean(beanObject,beanMethod,paramJson,_historyId,beanHistoryMethod);
		}
		if(mode==4){ //存储过程
			resultObj = templateDao.templateSearchByProcedure(templateTable, paramMap1);
		}
		return resultObj;
	}

	/**
	 *
	 * 调用spring bean 来查询数据
	 * @param beanObject
	 * @param beanMethod
	 * @param paramJson
	 * @param _historyId
	 * @param beanHistoryMethod
	 * @return
	 */
	private Result invokeBean(String beanObject, String beanMethod, String paramJson, String _historyId, String beanHistoryMethod){
		Result resultObj = new Result();
		Map<String, Object> resultMap = null;
		//重新解释页面传过来的参数
		Map<String,Object> paramMap1 = JsonMap.json2Map1(paramJson);
		try {
			//调用自定义的查询历史记录bean
			if(_historyId!=null && !StringUtils.isNull(beanHistoryMethod)){
				//动态调用bean
				resultObj = invokeBean(beanObject, beanHistoryMethod, paramMap1);
			}else{
				//动态调用bean
				resultObj = invokeBean(beanObject, beanMethod, paramMap1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return resultObj.fault(e.getLocalizedMessage());
		}  
		
		return resultObj.success();
	}

	/**
	 * 调用spring方法
	 * @param beanObject
	 * @param beanMethod
	 * @param paramMap
	 * @return
	 * @throws NoSuchMethodException
	 * @throws SecurityException
	 * @throws IllegalAccessException
	 * @throws IllegalArgumentException
	 * @throws InvocationTargetException
	 */
	private Result invokeBean(String beanObject, String beanMethod, Map<String,Object> paramMap) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		Object o = SpringContextHolder.getBean(beanObject);
		Method method = o.getClass().getMethod(beanMethod, Map.class);
		return (Result)method.invoke( o ,paramMap);
	}
	
	private Result invokeBean(String beanObject, String beanMethod, List<Map<String,Object>> list) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		Object o = SpringContextHolder.getBean(beanObject);
		Method method = o.getClass().getMethod(beanMethod, List.class);
		return (Result)method.invoke( o ,list);
	}
	
	@Override
	public A1TemplateTableDto getTemplateTable(String keyRandom){
		Search search = new Search();
		search.addFilterEqual("keyRandom", keyRandom);
		A1TemplateTable entity = (A1TemplateTable)a1TemplateTableDao.searchUnique(search);
		A1TemplateTableDto dto = new A1TemplateTableDto();
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateTableDto.class);
		assembler.assembleDto(dto, entity, null,  new BeanFactory() {
			@Override
			public Object get(String bean) {
				if("a1TemplateColumnDto".equals(bean)){
					return new A1TemplateColumnDto();
				}
				if("a1TemplateConditionDto".equals(bean)){
					return new A1TemplateConditionDto();
				}
				if("a1TemplateRightMenuDto".equals(bean)){
					return new A1TemplateRightMenuDto();
				}
				if("a1TemplateToolbarBtnDto".equals(bean)){
					return new A1TemplateToolbarBtnDto();
				}
				if("a1TemplateLineTailMenuDto".equals(bean)){
					return new A1TemplateLineTailMenuDto();
				}
				return null;
			}
		});
		String dataSource = entity.getDataSource();
		ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);
		//多值的列
		List<A1TemplateColumnDto> a1TemplateColumnDtoList = new ArrayList<A1TemplateColumnDto>();
		//将dataResoure=5，数据字典中数据，加载到column的option中
		List<A1TemplateColumn> columnList = entity.getA1TemplateColumnList();
		templateUtils.loadColumnOptionList(columnList);

		for(A1TemplateColumn column: columnList){
			A1TemplateColumnDto a1TemplateColumnDto = new A1TemplateColumnDto();
			DTOAssembler columnDtoAss = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
			columnDtoAss.assembleDto(a1TemplateColumnDto, column, null, null);
			if(column.getA1TemplateColumnOptionList().size()>0){ 
				for(A1TemplateColumnOption option: column.getA1TemplateColumnOptionList()){
					if(option.getDeleteFlag()==1)continue;
					String keyName = option.getKeyName();
					String valueName = option.getValueName();
					//int isDefault = option.getIsDefault();
					String optionName = option.getOptionName();
					A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
					a1TemplateColumnOptionDto.setId(option.getId());
					a1TemplateColumnOptionDto.setKeyName(keyName);
					a1TemplateColumnOptionDto.setValueName(valueName);
					//a1TemplateColumnOptionDto.setIsDefault(isDefault);
					a1TemplateColumnOptionDto.setOptionName(optionName);
					//hidden fileds
					for(A1TemplateColumnOptionHidCol hidCol : option.getA1TemplateColumnOptionHidColList()){
						A1TemplateColumnOptionHidColDto hidColDto = new A1TemplateColumnOptionHidColDto();
						hidColDto.setId(hidCol.getId());
						hidColDto.setColumName(hidCol.getColumName());
						a1TemplateColumnOptionDto.getA1TemplateColumnOptionHidColDtoList().add(hidColDto);
					}
					a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
				}
			}
			
			if(column.getDataResource() == A1TemplateColumnDto.dataResource_Sql
					|| column.getDataResource() == A1TemplateColumnDto.dataResource_Page){
				String optionsSql = column.getOptionsSql();
				if(!StringUtils.isNull(optionsSql)){ 
					String columnName = column.getColumnName();
					String defaultValue = column.getDefaultValue()==null?"":column.getDefaultValue();
					a1TemplateColumnDto.setColumnName(columnName);
					a1TemplateColumnDto.setDefaultValue(defaultValue);
					//sql 查询的表 可能是 业务数据库表，根据模板中的dataSource来区别
					List<Map<String,Object>> list = templateDao.queryReportBySearch(optionsSql);
					for(Map<String,Object> map : list){
						String keyName = String.valueOf(map.get("keyName"));
						String valueName = String.valueOf(map.get("valueName"));
						//int isDefault = 0;//option.getIsDefault();
						A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
						a1TemplateColumnOptionDto.setKeyName(keyName);
						a1TemplateColumnOptionDto.setValueName(valueName);
						//a1TemplateColumnOptionDto.setIsDefault(isDefault);
						a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
					}
				}
			}

			//默认的UI是 "easyui-textbox"
			if(StringUtils.isNull(column.getColumnType())){
				column.setColumnType("easyui-textbox");
			}

			a1TemplateColumnDtoList.add(a1TemplateColumnDto);
		}
		dto.setA1TemplateColumnDtoList(a1TemplateColumnDtoList);

		return dto;
	}

	/**
	 * 多值选择，key-value形式; case status when 1 then '有效' when 0 then '无效' end
	 * @param keyRandom
	 * @return
	 */
	@Override
	public String getTemplateTableColumnOptionJson(String keyRandom){
		Search search = new Search();
		search.addFilterEqual("keyRandom", keyRandom);
		A1TemplateTable a1TemplateTable = (A1TemplateTable)a1TemplateTableDao.searchUnique(search);
		//多值的列，拼成json返回给页面
		JSONArray jsonArrayColumnOptions = new JSONArray();
		for(A1TemplateColumn column: a1TemplateTable.getA1TemplateColumnList()){
			int dataResource = column.getDataResource();
			if(dataResource == A1TemplateColumnDto.dataResource_OptionsTable
					|| dataResource == A1TemplateColumnDto.dataResource_OptionsTableKeyEqualValue){
				getTemplateTableColumnOptionJson_OptionsTable(column,jsonArrayColumnOptions);
			}
			if( dataResource == A1TemplateColumnDto.dataResource_ColumnOptionData){
				getTemplateTableColumnOptionJson_OptionDataTable(column, jsonArrayColumnOptions);
			}
			if(dataResource == A1TemplateColumnDto.dataResource_Sql
					|| dataResource == A1TemplateColumnDto.dataResource_Page){
				getTemplateTableColumnOptionJson_Sql(column,jsonArrayColumnOptions,a1TemplateTable);
			}
		}
		String jsonArrayColumnOptionsStr = jsonArrayColumnOptions.toString();
		return jsonArrayColumnOptionsStr;
	}
	
	private void getTemplateTableColumnOptionJson_OptionsTable(A1TemplateColumn column,JSONArray jsonArrayColumnOptions){
		if(column.getA1TemplateColumnOptionList().size()>0){
			//json options
			JSONObject jsonObjectColumn = new JSONObject();
			jsonObjectColumn.put("columnName", column.getColumnName());
			jsonObjectColumn.put("defaultValue", column.getDefaultValue()==null?"":column.getDefaultValue());
			jsonObjectColumn.put("propertyName", StringUtils.switch2Property(column.getColumnName()));
			JSONArray jsonArrayOptions = new JSONArray();
			for(A1TemplateColumnOption option: column.getA1TemplateColumnOptionList()){
				if(option.getDeleteFlag()==1)continue;
				String keyName = option.getKeyName();
				String valueName=option.getValueName();
				//String isDefault=String.valueOf(option.getIsDefault());
				//json options
				JSONObject jsonObjectOption = new JSONObject();
				//jsonObjectOption.put(keyName, valueName);
				jsonObjectOption.put("keyName", keyName);
				jsonObjectOption.put("valueName", valueName);
				//jsonObjectOption.put("isDefault", isDefault);
				jsonObjectOption.put("optionName", option.getOptionName());
				jsonArrayOptions.put(jsonObjectOption);
			}
			jsonObjectColumn.put("columnOptions",jsonArrayOptions);
			jsonArrayColumnOptions.put(jsonObjectColumn);
		}
	}
	private void getTemplateTableColumnOptionJson_OptionDataTable(A1TemplateColumn column,JSONArray jsonArrayColumnOptions){
		//json options
		String columnName = column.getColumnName();
		JSONObject jsonObjectColumn = new JSONObject();
		jsonObjectColumn.put("columnName", columnName);
		jsonObjectColumn.put("defaultValue", column.getDefaultValue() == null ? "" : column.getDefaultValue());
		jsonObjectColumn.put("propertyName", StringUtils.switch2Property(columnName));
		JSONArray jsonArrayOptions = new JSONArray();

		//查询该列的数据字典
		Search search = new Search();
		search.addFilterEqual("columnName", columnName);
		search.addSort("sort", false);
		List<A1TemplateColumnOptionData> optionDataList = optionDataDao.search(search);
		for(A1TemplateColumnOptionData optionData : optionDataList){
			if(optionData.getDeleteFlag()==1)continue;
			long optionId = 0L;
			String keyName = optionData.getKeyName();
			String valueName=optionData.getValueName();
			String optionName=optionData.getOptionName();
			//json options
			JSONObject jsonObjectOption = new JSONObject();
			//jsonObjectOption.put(keyName, valueName);
			jsonObjectOption.put("keyName", keyName);
			jsonObjectOption.put("valueName", valueName);
			//jsonObjectOption.put("isDefault", 0);
			jsonObjectOption.put("optionName", optionName);
			jsonArrayOptions.put(jsonObjectOption);
		}
		jsonObjectColumn.put("columnOptions",jsonArrayOptions);
		jsonArrayColumnOptions.put(jsonObjectColumn);
	}


	private void getTemplateTableColumnOptionJson_Sql(A1TemplateColumn column,JSONArray jsonArrayColumnOptions, A1TemplateTable a1TemplateTable){
		ITemplateDao templateDao = templateServiceUtils.getTemplateDao(a1TemplateTable.getDataSource());

		String optionsSql = column.getOptionsSql();
		if(!StringUtils.isNull(optionsSql)){ 
			JSONObject jsonObjectColumn = new JSONObject();
			jsonObjectColumn.put("columnName", column.getColumnName());
			jsonObjectColumn.put("defaultValue", column.getDefaultValue()==null?"":column.getDefaultValue());
			jsonObjectColumn.put("propertyName", StringUtils.switch2Property(column.getColumnName()));
			JSONArray jsonArrayOptions = new JSONArray();

			List<Map<String,Object>> list = templateDao.queryReportBySearch(optionsSql);
			for(Map<String,Object> map : list){
				String keyName = String.valueOf(map.get("keyName"));
				String valueName = String.valueOf(map.get("valueName"));
				//String isDefault=String.valueOf(option.getIsDefault());
				//json options
				JSONObject jsonObjectOption = new JSONObject();
				//jsonObjectOption.put(keyName, valueName);
				jsonObjectOption.put("keyName", keyName);
				jsonObjectOption.put("valueName", valueName);
				//jsonObjectOption.put("isDefault", "0");
				jsonArrayOptions.put(jsonObjectOption);
				
			}
			jsonObjectColumn.put("columnOptions",jsonArrayOptions);
			jsonArrayColumnOptions.put(jsonObjectColumn);
			
		}
	}

	/**
	 * 多值选择，key-value形式; case status when 1 then '有效' when 0 then '无效' end
	 * @param keyRandom
	 * @return
	 */
	@Override
	public List<A1TemplateColumnDto> getTemplateTableColumnOptionList(String keyRandom){
		Search search = new Search();
		search.addFilterEqual("keyRandom", keyRandom);
		A1TemplateTable a1TemplateTable = (A1TemplateTable)a1TemplateTableDao.searchUnique(search);
		//多值的列，拼成json返回给页面
		List<A1TemplateColumnDto> a1TemplateColumnDtoList = new ArrayList<A1TemplateColumnDto>();
		//通过配置的options 表来加载数据
		loadTemplateTableColumnOptionListFromOptionsTable(a1TemplateTable, a1TemplateColumnDtoList);
		//从加载的SQL语句来读新的表加载数据
		loadTemplateTableColumnOptionListFromSql(a1TemplateTable,a1TemplateColumnDtoList);
		
		//多值的列，加入json返回
		for(A1TemplateColumnDto columnDto : a1TemplateColumnDtoList){
			JSONArray ja = new JSONArray();
			for(A1TemplateColumnOptionDto optionDto : columnDto.getA1TemplateColumnOptionDtoList()){
				JSONObject jo = new JSONObject();
				jo.put("optionId", optionDto.getId());
				jo.put("valueName", optionDto.getValueName());
				jo.put("keyName", optionDto.getKeyName());
				jo.put("optionName", optionDto.getOptionName());
				ja.put(jo);
			}
			columnDto.setA1TemplateColumnOptionJsonArray(ja.toString());
		}
		
		return a1TemplateColumnDtoList;	
	}
	

	/**
	 * 通过OptionId 查找 option hidden column
	 * @param optionId
	 * @return
	 */
	@Override
	public List<A1TemplateColumnOptionHidColDto> queryOptionHidColByOptionId(long optionId){
		Search search = new Search();
		search.addFilterEqual("deleteFlag", 0);
		search.addFilterEqual("a1TemplateColumnOption.id", optionId);
		List<A1TemplateColumnOptionHidCol> a1TemplateColumnOptionHidColList = optionHidColDao.search(search);
		List<A1TemplateColumnOptionHidColDto> a1TemplateColumnOptionHidColDtoList = new ArrayList<A1TemplateColumnOptionHidColDto>();
		
		//Assembler
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnOptionHidColDto.class);
		assembler.assembleDtos(a1TemplateColumnOptionHidColDtoList, a1TemplateColumnOptionHidColList, null, null);
		
		return a1TemplateColumnOptionHidColDtoList;	
	}

	/**
	 * 从加载的SQL语句来读新的表加载数据
	 * @param a1TemplateTable
	 * @param a1TemplateColumnDtoList
	 * @return
	 */
	private void loadTemplateTableColumnOptionListFromSql(A1TemplateTable a1TemplateTable,List<A1TemplateColumnDto> a1TemplateColumnDtoList){
		ITemplateDao templateDao = templateServiceUtils.getTemplateDao(a1TemplateTable.getDataSource());

		for(A1TemplateColumn column: a1TemplateTable.getA1TemplateColumnList()){
			int dataResource = column.getDataResource();
			if(dataResource == A1TemplateColumnDto.dataResource_Sql){
				String optionsSql = column.getOptionsSql();
				if(!StringUtils.isNull(optionsSql)){ 
					String columnName = column.getColumnName();
					A1TemplateColumnDto a1TemplateColumnDto = new A1TemplateColumnDto();
					a1TemplateColumnDto.setColumnName(columnName);

					DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
					assembler.assembleDto(a1TemplateColumnDto, column, null, null);

					List<Map<String,Object>> list = templateDao.queryReportBySearch(optionsSql);
					for(Map<String,Object> map : list){
						String keyName = String.valueOf(map.get("keyName"));
						String valueName = String.valueOf(map.get("valueName"));
						A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
						a1TemplateColumnOptionDto.setKeyName(keyName);
						a1TemplateColumnOptionDto.setValueName(valueName);
						a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
					}
					a1TemplateColumnDtoList.add(a1TemplateColumnDto);
					
				}
			}
		}
	} 
	
	/**
	 * 从 A1TemplateColumnOption 表中加载数据
	 * @param a1TemplateTable
	 * @param a1TemplateColumnDtoList
	 * @return
	 */
	private void loadTemplateTableColumnOptionListFromOptionsTable(A1TemplateTable a1TemplateTable,List<A1TemplateColumnDto> a1TemplateColumnDtoList){
		for(A1TemplateColumn column: a1TemplateTable.getA1TemplateColumnList()){
			int dataResource = column.getDataResource();
			if(dataResource == A1TemplateColumnDto.dataResource_OptionsTable
					|| dataResource == A1TemplateColumnDto.dataResource_OptionsTableKeyEqualValue){
				if(column.getA1TemplateColumnOptionList().size()>0){ 
					String columnName = column.getColumnName();
					A1TemplateColumnDto a1TemplateColumnDto = new A1TemplateColumnDto();
					a1TemplateColumnDto.setColumnName(columnName);

					DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
					assembler.assembleDto(a1TemplateColumnDto, column, null, null);

					for(A1TemplateColumnOption option: column.getA1TemplateColumnOptionList()){
						if(option.getDeleteFlag()==1)continue;
						long optionId = option.getId();
						String keyName = option.getKeyName();
						String valueName=option.getValueName();
						String optionName=option.getOptionName();
						A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
						a1TemplateColumnOptionDto.setId(optionId);
						a1TemplateColumnOptionDto.setKeyName(keyName);
						a1TemplateColumnOptionDto.setValueName(valueName);
						a1TemplateColumnOptionDto.setOptionName(optionName);
						a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
					}
					a1TemplateColumnDtoList.add(a1TemplateColumnDto);
				}
			}else if(dataResource == A1TemplateColumnDto.dataResource_ColumnOptionData){
				String columnName = column.getColumnName();
				A1TemplateColumnDto a1TemplateColumnDto = new A1TemplateColumnDto();
				a1TemplateColumnDto.setColumnName(columnName);
				//查询该列的数据字典
				Search search = new Search();
				search.addFilterEqual("columnName",columnName);
				search.addSort("sort",false);
				List<A1TemplateColumnOptionData> optionDataList = optionDataDao.search(search);
				for(A1TemplateColumnOptionData optionData : optionDataList){
					if(optionData.getDeleteFlag()==1)continue;
					long optionId = 0L;
					String keyName = optionData.getKeyName();
					String valueName=optionData.getValueName();
					String optionName=optionData.getOptionName();
					A1TemplateColumnOptionDto a1TemplateColumnOptionDto = new A1TemplateColumnOptionDto();
					a1TemplateColumnOptionDto.setId(optionId);
					a1TemplateColumnOptionDto.setKeyName(keyName);
					a1TemplateColumnOptionDto.setValueName(valueName);
					a1TemplateColumnOptionDto.setOptionName(optionName);
					a1TemplateColumnDto.getA1TemplateColumnOptionDtoList().add(a1TemplateColumnOptionDto);
				}
				a1TemplateColumnDtoList.add(a1TemplateColumnDto);
			}
		}
	} 

	
	/*
	@Override
	public List<A1TemplateTableDto> getColumnList(String keyRandom) {
		Search search = new Search();
		search.addFilterEqual("keyRandom", keyRandom);
		List<A1TemplateTable> entityList =  a1TemplateTableDao.search(search);
		final List<A1TemplateTableDto> dtoList = new ArrayList<A1TemplateTableDto>();
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateTableDto.class);
		assembler.assembleDtos(dtoList, entityList, null,  null);
		return dtoList;
	}
	
	/*
	@Override
	public List<Map<String,String>> getCondition(String keyRandom) {
		List<Map<String,String>> list = templateTemlDao.getCondition(keyRandom);
		return list;
	}
	*/
	
	@Override
	public Map<String, Object> getConditionMultiQuery(String keyRandom) {
		StringBuilder sb = new StringBuilder();
		
		Search search = new Search();
		search.addFilterEqual("a1TemplateTable.keyRandom", keyRandom);
		search.addFilterEqual("isMultiQuery", 1);
		List<A1TemplateCondition> conditionList = a1TemplateConditionDao.search(search);
		
		List<A1TemplateConditionDto> conditionDtoList = new ArrayList<A1TemplateConditionDto>();
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateConditionDto.class);
		assembler.assembleDtos(conditionDtoList, conditionList, null, new BeanFactory() {
			@Override
			public Object get(String bean) {
				if ("a1TemplateConditionMultiQueryDto".equals(bean)) {
					return new A1TemplateConditionMultiQueryDto();
				}
				return null;
			}
		});
		
		//datagridPropertyMultiQuery 页面要的字符串
		String datagridPropertyMultiQuery = "";
		if(conditionDtoList!=null && conditionDtoList.size()>0){
			for(A1TemplateConditionDto conditionDto:conditionDtoList){
				String columnName = conditionDto.getColumnName();
				String columnShowName = conditionDto.getColumnShowName();
				sb.append(columnName).append(":'").append(columnShowName).append("',");
			}
			sb.delete(sb.length()-1,sb.length());
			datagridPropertyMultiQuery = sb.toString();
		}

		//select[key,value]
		List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
		if(conditionDtoList!=null){
			for(A1TemplateConditionDto conditionDto:conditionDtoList){
				Map<String, Object> mapMiddle = new HashMap<String, Object>();
				Map<String, Object> mapInner = new HashMap<String, Object>();
				
				long A1TemplateCondition_Id = conditionDto.getId();
				String columnName = conditionDto.getColumnName();
				
				search.clear();
				search.addFilterEqual("a1TemplateCondition.id", A1TemplateCondition_Id);
				List<A1TemplateConditionMultiQuery> a1TemplateConditionMultiQueryList = a1TemplateConditionMultiQueryDao.search(search);
				for(A1TemplateConditionMultiQuery a1TemplateConditionMultiQuery: a1TemplateConditionMultiQueryList){
					mapInner.put(a1TemplateConditionMultiQuery.getKeyName(), a1TemplateConditionMultiQuery.getValueName());
				}
				mapMiddle.put("columnName", columnName);
				mapMiddle.put("mapInner", mapInner);
				list.add(mapMiddle);
			}
		}
		
		Map<String, Object> mapOut = new HashMap<String, Object>();
		mapOut.put("datagridPropertyMultiQueryString", datagridPropertyMultiQuery);
		mapOut.put("datagridPropertyMultiQueryList", list);
		
		return  mapOut;
	}
	
	@Override
	public List<Map<String, Object>> queryReportBySearch(A1TemplateTableDto templateTableDto,String reportSqlString,List<Object> paramList) {
		Search search = new Search();
		search.addFilterEqual("keyRandom", templateTableDto.getKeyRandom());
		A1TemplateTable templateTable = (A1TemplateTable)a1TemplateTableDao.searchUnique(search);
		//加载数据源
		String dataSource = templateTable.getDataSource();
		ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);
		return templateDao.queryListBySearch(reportSqlString, paramList);
	}
	
	/*
	@Override
	public List<Map<String, Object>> getRightMenuList(String keyRandom) {
		Map<String,Object> templateTable = templateDao.getTemplateTable(keyRandom);
		long tableId = Long.parseLong(templateTable.get("id").toString());
		return templateDao.getRightMenuList(tableId);
	}
	*/
	

	@Override
	public Result addA1TemplateTable(A1TemplateTableDto a1TemplateTableDto) {
		Result result = new Result();
		String tableName = a1TemplateTableDto.getTableName();
		//判断表是否存在,不存在就像数据库里create table
		try{
			templateTemlDao.createTableNotExist(tableName);
		}catch (Exception e) {
			e.printStackTrace();
			return result.fault("创建表失败");
		}
		
		A1TemplateTable a1TemplateTable = new A1TemplateTable();
		DTOAssembler ass = DTOAssembler.newAssembler(A1TemplateTableDto.class);
		ass.assembleEntity(a1TemplateTableDto, a1TemplateTable, null, null);
		a1TemplateTableDao.create(a1TemplateTable);
		return result.success();
	}



	@Override
	public Result addA1TemplateColumn2Table(A1TemplateColumnDto a1TemplateColumnDto, long tableId) {
		Result result = new Result();
		A1TemplateColumn a1TemplateColumn = new A1TemplateColumn();
		DTOAssembler ass = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
		ass.assembleEntity(a1TemplateColumnDto, a1TemplateColumn, null, null);
		a1TemplateColumn.setA1TemplateTable(a1TemplateTableDao.fetch(tableId));
		a1TemplateColumnDao.create(a1TemplateColumn);
		return result.success();
	}
	
	@Override
	public Result addA1TemplateCondition2Table(A1TemplateConditionDto a1TemplateConditionDto, long tableId) {
		Result result = new Result();
		A1TemplateCondition a1TemplateCondition = new A1TemplateCondition();
		DTOAssembler ass = DTOAssembler.newAssembler(A1TemplateConditionDto.class);
		ass.assembleEntity(a1TemplateConditionDto, a1TemplateCondition, null, null);
		a1TemplateCondition.setA1TemplateTable(a1TemplateTableDao.fetch(tableId));
		a1TemplateConditionDao.create(a1TemplateCondition);
		return result.success();
	}

	@Override
	public Result addA1TemplateMultiQuery2Condition(A1TemplateConditionMultiQueryDto a1TemplateConditionMultiQueryDto, long conditionId) {
		Result result = new Result();
		A1TemplateConditionMultiQuery a1TemplateConditionMultiQuery = new A1TemplateConditionMultiQuery();
		DTOAssembler ass = DTOAssembler.newAssembler(A1TemplateConditionMultiQueryDto.class);
		ass.assembleEntity(a1TemplateConditionMultiQueryDto, a1TemplateConditionMultiQuery, null, null);
		a1TemplateConditionMultiQuery.setA1TemplateCondition(a1TemplateConditionDao.fetch(conditionId));
		a1TemplateConditionMultiQueryDao.create(a1TemplateConditionMultiQuery);
		return result.success();
	}
	

	@Override
	public Result addA1TemplateRightMenu2Table(A1TemplateRightMenuDto a1TemplateRightMenuDto, long tableId) {
		Result result = new Result();
		A1TemplateRightMenu a1TemplateRightMenu = new A1TemplateRightMenu();
		DTOAssembler ass = DTOAssembler.newAssembler(A1TemplateRightMenuDto.class);
		ass.assembleEntity(a1TemplateRightMenuDto, a1TemplateRightMenu, null, null);
		a1TemplateRightMenu.setA1TemplateTable(a1TemplateTableDao.fetch(tableId));
		a1TemplateRightMenuDao.create(a1TemplateRightMenu);
		return result.success();
	}
	
	@Override
	public PaginationDto<A1TemplateTableDto> paginationA1TemplateTableListBySearch(Search search) {
    	PaginationService<A1TemplateTableDto> paginationService = new PaginationService<A1TemplateTableDto>(search);
		if(!paginationService.validataPageSize()){return null;};
		SearchResult<A1TemplateTable> searchResult =  a1TemplateTableDao.searchAndCount(search);
		List<A1TemplateTable> entityList = searchResult.getResult();
		
		int totalCount = searchResult.getTotalCount();
		final List<A1TemplateTableDto> dtoList = new ArrayList<A1TemplateTableDto>();
		
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateTableDto.class);
		assembler.assembleDtos(dtoList, entityList, null, null);
		paginationService.assemblePaginationDto(dtoList, totalCount);
		
		return paginationService.getPaginationDto();
	}
	

	
	@Override
	public PaginationDto<A1TemplateColumnDto> paginationA1TemplateColumnListBySearch(Search search) {
    	PaginationService<A1TemplateColumnDto> paginationService = new PaginationService<A1TemplateColumnDto>(search);
		if(!paginationService.validataPageSize()){return null;};
		SearchResult<A1TemplateColumn> searchResult =  a1TemplateColumnDao.searchAndCount(search);
		List<A1TemplateColumn> entityList = searchResult.getResult();
		
		int totalCount = searchResult.getTotalCount();
		final List<A1TemplateColumnDto> dtoList = new ArrayList<A1TemplateColumnDto>();
		
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnDto.class);
		assembler.assembleDtos(dtoList, entityList, null, null);
		paginationService.assemblePaginationDto(dtoList, totalCount);
		
		return paginationService.getPaginationDto();
	}
	

	@Override
	public PaginationDto<A1TemplateConditionDto> paginationA1TemplateConditionListBySearch(Search search) {
    	PaginationService<A1TemplateConditionDto> paginationService = new PaginationService<A1TemplateConditionDto>(search);
		if(!paginationService.validataPageSize()){return null;};
		SearchResult<A1TemplateCondition> searchResult =  a1TemplateConditionDao.searchAndCount(search);
		List<A1TemplateCondition> entityList = searchResult.getResult();
		
		int totalCount = searchResult.getTotalCount();
		final List<A1TemplateConditionDto> dtoList = new ArrayList<A1TemplateConditionDto>();
		
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateConditionDto.class);
		assembler.assembleDtos(dtoList, entityList, null,  null);
		paginationService.assemblePaginationDto(dtoList, totalCount);
		
		return paginationService.getPaginationDto();
	}
	
	
	@Override
	public PaginationDto<A1TemplateConditionMultiQueryDto> paginationA1TemplateConditionMultiQueryListBySearch(Search search) {
    	PaginationService<A1TemplateConditionMultiQueryDto> paginationService = new PaginationService<A1TemplateConditionMultiQueryDto>(search);
		if(!paginationService.validataPageSize()){return null;};
		SearchResult<A1TemplateConditionMultiQuery> searchResult =  a1TemplateConditionMultiQueryDao.searchAndCount(search);
		List<A1TemplateConditionMultiQuery> entityList = searchResult.getResult();
		
		int totalCount = searchResult.getTotalCount();
		final List<A1TemplateConditionMultiQueryDto> dtoList = new ArrayList<A1TemplateConditionMultiQueryDto>();
		
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateConditionMultiQueryDto.class);
		assembler.assembleDtos(dtoList, entityList, null,  null);
		paginationService.assemblePaginationDto(dtoList, totalCount);
		
		return paginationService.getPaginationDto();
	}
	

	@Override
	public PaginationDto<A1TemplateRightMenuDto> paginationA1TemplateRightMenuListBySearch(Search search) {
    	PaginationService<A1TemplateRightMenuDto> paginationService = new PaginationService<A1TemplateRightMenuDto>(search);
		if(!paginationService.validataPageSize()){return null;};
		SearchResult<A1TemplateRightMenu> searchResult =  a1TemplateRightMenuDao.searchAndCount(search);
		List<A1TemplateRightMenu> entityList = searchResult.getResult();
		
		int totalCount = searchResult.getTotalCount();
		final List<A1TemplateRightMenuDto> dtoList = new ArrayList<A1TemplateRightMenuDto>();
		
		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateRightMenuDto.class);
		assembler.assembleDtos(dtoList, entityList, null,  null);
		paginationService.assemblePaginationDto(dtoList, totalCount);
		
		return paginationService.getPaginationDto();
	}
	

	/**
	 * 添加数据
	 * @param keyRandom
	 * @param jsonParam
	 * @return
	 * @throws SQLException 
	 * @throws HibernateException 
	 */
	@Override
	public Result addData(String keyRandom, String jsonParam) throws HibernateException, SQLException {
		Result result = new Result();
		try{
			Search search = new Search();
			JSONObject json = new JSONObject(jsonParam);

			//查询表名
			search.addFilterEqual("keyRandom", keyRandom);
			List<A1TemplateTable> a1TemplateTableList = a1TemplateTableDao.search(search);
			A1TemplateTable a1TemplateTable = a1TemplateTableList.get(0);

			//取要添加和编辑的列
			List<Map<String,Object>> columnList = getColumnList(a1TemplateTable, json);

			//result = templateService.addData4DynamicDS(a1TemplateTable, json, columnList);
			result = addData4DynamicDS(a1TemplateTable, json, columnList);
		}catch (Exception e) {
			e.printStackTrace();
			return result.fault(e.getLocalizedMessage());
		}
		return result;
	}

	/**
	 * 向指定的数据库表中insert
	 * @param a1TemplateTable
	 * @param json
	 * @param columnList
	 * @return
	 * @throws Exception
	 */
	@Override
	public Result addData4DynamicDS(A1TemplateTable a1TemplateTable, JSONObject json,List<Map<String,Object>> columnList) throws Exception{
		Result result = new Result();

		//create man
		String value = json.optJSONArray("createMan").get(0).toString();
		Map map = new HashMap();
		map.put(Column_Constant.COLUMN_NAME, "createMan");
		map.put(Column_Constant.COLUMN_VALUE,value);
		columnList.add(map);
		//create date
		map = new HashMap();
		map.put(Column_Constant.COLUMN_NAME, "createDate");
		map.put(Column_Constant.COLUMN_VALUE,new java.sql.Timestamp(new Date().getTime()));
		map.put(Column_Constant.DATA_TYPE,"datetime");
		columnList.add(map);

		//加入intercept
		Result interceptResult = intercept4Add(a1TemplateTable, columnList);
		if(interceptResult.isSuccess() && "continue".equals(interceptResult.getRemark())){
			//result = templateService.addData4DynamicDSInner(a1TemplateTable,columnList);

			String dataSource = a1TemplateTable.getDataSource();
			ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);
			templateDao.addData(a1TemplateTable.getTableName(), columnList);
			/*
			if(dataSource!=null && dataSource.equals(ConstantDto.bizDataSource)){
				templateDao.addData(a1TemplateTable.getTableName(), columnList);
			}else{
				templateTemlDao.addData(a1TemplateTable.getTableName(), columnList);
			}
			*/

			return result.success();
		}else{
			return interceptResult;
		}
	}

	/*
	@Transactional(propagation = Propagation.NOT_SUPPORTED)
	@Override
	public Result addData4DynamicDSInner(A1TemplateTable a1TemplateTable,List<Map<String,Object>> columnList) throws Exception{
		Result result = new Result();

		//加载数据源
		String dataSource = a1TemplateTable.getDataSource();
		DynamicDataSourceHolder.setDataSource(dataSource);

		templateDao.addData(a1TemplateTable.getTableName(), columnList);

		return result.success();
	}
	*/

	/**
	 * 编辑数据
	 * @param keyRandom
	 * @param id
	 * @param jsonParam
	 * @return
	 */
	@Override
	public Result editData(String keyRandom, String id, String jsonParam) throws HibernateException, SQLException{
		Result result = new Result();
		try{
			Search search = new Search();
			JSONObject json = new JSONObject(jsonParam);

			//查询表名
			search.addFilterEqual("keyRandom", keyRandom);
			List<A1TemplateTable> a1TemplateTableList = a1TemplateTableDao.search(search);
			A1TemplateTable a1TemplateTable = a1TemplateTableList.get(0);
			//取要添加和编辑的列
			List<Map<String,Object>> columnList = getColumnList(a1TemplateTable, json);

			//result = templateService.editData4DynamicDS(a1TemplateTable, id, json, columnList);
			result = editData4DynamicDS(a1TemplateTable, id, json, columnList);
		}catch (Exception e) {
			e.printStackTrace();
			return result.fault(e.getLocalizedMessage());
		}
		return result;
	}

	@Override
	public Result editData4DynamicDS(A1TemplateTable a1TemplateTable,String id, JSONObject json,List<Map<String,Object>> columnList) throws Exception{
		Result result = new Result();

		//modify man
		String value = json.optJSONArray("createMan").get(0).toString();
		Map map = new HashMap();
		map.put(Column_Constant.COLUMN_NAME, "modifyMan");
		map.put(Column_Constant.COLUMN_VALUE,value);
		columnList.add(map);
		//modify date
		map = new HashMap();
		map.put(Column_Constant.COLUMN_NAME, "modifyDate");
		map.put(Column_Constant.COLUMN_VALUE,new java.sql.Timestamp(new Date().getTime()));
		map.put(Column_Constant.DATA_TYPE,"datetime");
		columnList.add(map);

		//加入intercept
		Result interceptResult = intercept4Edit(a1TemplateTable,columnList,id);
		if(interceptResult.isSuccess() && "continue".equals(interceptResult.getRemark())){
			//templateService.editData4DynamicDSInner(a1TemplateTable,id,columnList);
			String dataSource = a1TemplateTable.getDataSource();
			ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);
			templateDao.editData(a1TemplateTable.getTableName(), id, columnList);
			/*
			if(dataSource!=null && dataSource.equals(ConstantDto.bizDataSource)){
				templateDao.editData(a1TemplateTable.getTableName(), id, columnList);
			}else{
				templateTemlDao.editData(a1TemplateTable.getTableName(), id, columnList);
			}
			*/

		}else{
			return interceptResult;
		}

		return result.success();
	}

	@Override
	public Result removeData(String ... parameters){
		Result result = new Result();
		String _keyRandom = parameters[0];
		String _id = parameters[1];
		String createMan = parameters[2];

		//查询表名
		Search search = new Search();
		search.addFilterEqual("keyRandom", _keyRandom);
		List<A1TemplateTable> a1TemplateTableList = a1TemplateTableDao.search(search);
		A1TemplateTable a1TemplateTable = a1TemplateTableList.get(0);

		//update 列数据
		List<Map<String,Object>> columnList = new ArrayList<>();
		Map<String,Object> map = new HashMap<String,Object>();
		map.put(Column_Constant.COLUMN_NAME, "deleteflag");
		map.put(Column_Constant.COLUMN_VALUE,"1");
		map.put(Column_Constant.DATA_TYPE,"int");
		columnList.add(map);
		//modify man
		String value = createMan;
		map = new HashMap();
		map.put(Column_Constant.COLUMN_NAME, "modifyMan");
		map.put(Column_Constant.COLUMN_VALUE,value);
		columnList.add(map);
		//modify date
		map = new HashMap();
		map.put(Column_Constant.COLUMN_NAME, "modifyDate");
		map.put(Column_Constant.COLUMN_VALUE,new java.sql.Timestamp(new Date().getTime()));
		map.put(Column_Constant.DATA_TYPE,"datetime");
		columnList.add(map);

		//动态数据源
		String dataSource = a1TemplateTable.getDataSource();
		ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);
		try {
			templateDao.editData(a1TemplateTable.getTableName(), _id, columnList);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("删除数据出错");
		}
		return result.success();
	}



	/**
	 * intercept invoke my own spring method when add or edit data
	 * @param a1TemplateTable
	 * @return
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws IllegalAccessException 
	 * @throws SecurityException 
	 * @throws NoSuchMethodException 
	 */
	private Result intercept4Add(A1TemplateTable a1TemplateTable,List<Map<String,Object>> columnList) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		Result result = new Result();
		//interceptObject,interceptMethod
		String interceptObject = a1TemplateTable.getInterceptObject();
		String interceptMethod = a1TemplateTable.getInterceptAddMethod();
		
		if(StringUtils.isNull(interceptObject) || StringUtils.isNull(interceptMethod)){
			result.setRemark("continue");
			return result.success();
		}
		//调用bean
		Result invodeBeanResult = invokeBean(interceptObject, interceptMethod, columnList);
		
		return invodeBeanResult;
	}

	private Result intercept4Edit(A1TemplateTable a1TemplateTable,List<Map<String,Object>> columnList,String id) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		Result result = new Result();
		//interceptObject,interceptMethod
		String interceptObject = a1TemplateTable.getInterceptObject();
		String interceptMethod = a1TemplateTable.getInterceptEditMethod();
		
		if(StringUtils.isNull(interceptObject) || StringUtils.isNull(interceptMethod)){
			result.setRemark("continue");
			return result.success();
		}
		//调用bean
		Result invodeBeanResult = invokeBean(interceptObject, interceptMethod, columnList);
		
		return invodeBeanResult;
	}
	
	/**
	 * 取模板中要添加和编辑的列
	 * @param a1TemplateTable
	 * @return
	 */
	private List<Map<String,Object>> getColumnList(A1TemplateTable a1TemplateTable, JSONObject json){

		//取出每一列的data_tranfer
		Map<String,A1TemplateColumn> a1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(a1TemplateTable);

		Search search = new Search();
		search.clear();
		search.addFilterEqual("a1TemplateTable.id", a1TemplateTable.getId());
		search.addFilterEqual("canAdd", "1");
		search.addFilterEqual("deleteFlag", "0");
		List<A1TemplateColumn> a1TemplateColumnList1 = a1TemplateColumnDao.search(search);
		List<Map<String,Object>> columnList = new ArrayList<Map<String,Object>>();
		for(A1TemplateColumn column : a1TemplateColumnList1){
			String columnName = column.getColumnName();
			if(json.optJSONArray(columnName)==null){continue;}
			String value = json.optJSONArray(columnName).get(0).toString().trim();

			//转换 data_transfer
			//取列的模板对象
			A1TemplateColumn a1TemplateColumn = a1TemplateColumnMap.get(columnName);
			if(a1TemplateColumn!=null) {
				String dataTransfer = a1TemplateColumn.getDataTransfer();
				//金额转换
				if ("fen2yuan".equalsIgnoreCase(dataTransfer)) {
					value = FenAndYuan.yuan2fenStr(value);
				}
			}

			Map<String,Object> map = new HashMap<String,Object>();
			map.put(Column_Constant.COLUMN_NAME, columnName);
			map.put(Column_Constant.COLUMN_VALUE,value);
			map.put(Column_Constant.DATA_TYPE,column.getColumnDataType());
			columnList.add(map);
		}
		
		return columnList;
	}
	
	/**
	 * 自动加载模板列
	 * @param _id
	 * @return
	 */
	@Override
	public Result loadColumn(String _id){
		Result resultObj = new Result();
		try{
			A1TemplateTable templateTable = (A1TemplateTable)a1TemplateTableDao.fetch(Long.valueOf(_id));
			//模板中所有列
			List<String> tempColList = new ArrayList<String>();
			for(A1TemplateColumn tmpCol:templateTable.getA1TemplateColumnList()){
				tempColList.add(tmpCol.getColumnName());
			}
			//resultObj = templateService.loadColumn4DynamicDS(templateTable,tempColList);
			resultObj = loadColumn4DynamicDS(templateTable,tempColList);
		}catch (Exception e) {
			e.printStackTrace();
		}
		return resultObj;//resultObj.success();
	}

	@Override
	public Result loadColumn4DynamicDS(A1TemplateTable templateTable, List<String> tempColList){
		Result resultObj = new Result();

		//加载数据源
		String dataSource = templateTable.getDataSource();
		ITemplateDao templateDao = templateServiceUtils.getTemplateDao(dataSource);

		//取PrimaryKey
		String pKey = templateDao.loadTablePrimaryKey(templateTable);
		//.out.println("======pKey:"+pKey);
		//取列List
		List<Map<String, Object>> list = templateDao.loadColumn(templateTable);
		List<A1TemplateColumn> a1TemplateColumnList = new ArrayList<>();
		for(Map<String,Object> columnMap : list){
			String columnName = (String)columnMap.get("name");
			String data_type = (String)columnMap.get("data_type");
			//.out.println("======columnName1:"+columnName);
			//.out.println("======data_type1:"+data_type);
			//列是否存在模板列中了
			if(tempColList.contains(columnName))continue;


			//添加模板列
			A1TemplateColumn templateColumn = new A1TemplateColumn();
			templateColumn.setColumnShowName(columnName);
			templateColumn.setColumnName(columnName);
			templateColumn.setA1TemplateTable(templateTable);
			templateColumn.setCanAdd(1);

			//primary key 不可以add, 系统自动生成
			if(columnName.equals(pKey)){
				templateColumn.setColumnShowName("ID");
				templateColumn.setCanAdd(0);
			}
			//创建日期
			if(columnName.contains("createDate")){
				if(Config.db_dialect.equals("mssqlserver")) {
					templateColumn.setColumnNameSql("convert(varchar,createDate,120)");
				}
				else if(Config.db_dialect.equals("postgresql")) {
					templateColumn.setColumnNameSql("to_char(createDate, 'YYYY-MM-DD HH24:MI:SS')");
				}
				templateColumn.setColumnType("date");
				templateColumn.setColumnShowName("创建日期");
				templateColumn.setCanAdd(0);
			}
			//修改日期
			if(columnName.contains("modifyDate")){
				if(Config.db_dialect.equals("mssqlserver")) {
					templateColumn.setColumnNameSql("convert(varchar,modifyDate,120)");
				}
				else if(Config.db_dialect.equals("postgresql")) {
					templateColumn.setColumnNameSql("to_char(modifyDate, 'YYYY-MM-DD HH24:MI:SS')");
				}
				templateColumn.setColumnType("date");
				templateColumn.setColumnShowName("修改日期");
				templateColumn.setCanAdd(0);
			}
			//创建人
			if(columnName.contains("createMan")){
				templateColumn.setColumnShowName("创建人");
				templateColumn.setCanAdd(0);
			}
			//修改人
			if(columnName.contains("modifyMan")){
				templateColumn.setColumnShowName("修改人");
				templateColumn.setCanAdd(0);
			}
			//删除标志
			if(columnName.contains("deleteFlag")){
				templateColumn.setColumnShowName("删除标志");
				templateColumn.setColumnType("combobox");
				templateColumn.setDefaultValue("0");
				templateColumn.setDataResource(1);
				templateColumn.setIsQuery(1);

				//add options
				A1TemplateColumnOption option = null;
				option = new A1TemplateColumnOption();
				option.setKeyName("1");
				option.setValueName("已删除");
				option.setA1TemplateColumn(templateColumn);
				optionDao.create(option);

				option = new A1TemplateColumnOption();
				option.setKeyName("0");
				option.setValueName("正常");
				//option.setIsDefault(1);
				option.setA1TemplateColumn(templateColumn);
				optionDao.create(option);
			}
			templateColumn.setColumnDataType(data_type);//字段类型
			a1TemplateColumnList.add(templateColumn);
		}

		//添加到模板表里
		//templateService.addTemplateColumn(a1TemplateColumnList);
		for(A1TemplateColumn templateColumn:a1TemplateColumnList) {
			a1TemplateColumnDao.create(templateColumn);
		}

		return resultObj.success();
	}

/*
	@Resource
	IMenuService menuService;

	@Override
	public Result template2menuALL(long _templateTableId, long menu1Id, String pageType, String menu2Name){
		if(pageType.equals("datagrid")){
			return menuService.template2menu(_templateTableId, menu1Id, menu2Name);
		}else if(pageType.equals("datagrid-sourcecode")){
			return menuService.template2menuSourceCode(_templateTableId, menu1Id,menu2Name);
		}else{
			return menuService.template2menu4Chart(_templateTableId, menu1Id, pageType, menu2Name);
		}
	}*/
/*

	@Override
	public Result template2menu(long _templateTableId, long menu1Id, String menu2Name) {
		Result result = new Result();
		//取模板表
		A1TemplateTable table = a1TemplateTableDao.fetch(_templateTableId);
		String tableNameShow = table.getTableNameShow();
		String keyRandom = table.getKeyRandom();
		//menu2Name
		menu2Name = StringUtils.isNull(menu2Name)?tableNameShow:menu2Name;

		Search search = new Search();
		search.addFilterEqual("text", menu2Name);
		search.addFilterEqual("deleteFlag", "0");
		int count = menu2Dao.count(search);
		if(count > 0){
			return result.fault("已存在二级菜单，可删除原菜单后继续："+menu2Name);
		}

		//取一级菜单
		A2Menu1 menu1 = menu1Dao.fetch(menu1Id);
		//生成二级菜单
		A2Menu2 menu2 = new A2Menu2();
		menu2.setText(menu2Name);
		menu2.setA2Menu1(menu1);
		menu2.setUrl(Config.url_queryPage+keyRandom);
		menu2Dao.create(menu2);
		//生成功能资源
		A2MenuFunction fun = new A2MenuFunction();
		fun.setUrl(Config.url_queryAction+keyRandom);
		fun.setAccessTypeLevel(0);
		fun.setText("query");
		fun.setA2Menu2(menu2);
		menuFunctionDao.create(fun);
		//生成History query的资源
		A2MenuFunction funHis = new A2MenuFunction();
		funHis.setUrl(Config.url_historyPage+keyRandom);
		funHis.setAccessTypeLevel(0);
		funHis.setText("history");
		funHis.setA2Menu2(menu2);
		menuFunctionDao.create(funHis);
		//report download
		A2MenuFunction funDown = new A2MenuFunction();
		funDown.setUrl(Config.url_reportPage+keyRandom);
		funDown.setAccessTypeLevel(0);
		funDown.setText("report");
		funDown.setA2Menu2(menu2);
		menuFunctionDao.create(funDown);
		//生成添加、修改的资源
		A2MenuFunction funAdd = new A2MenuFunction();
		funAdd.setUrl(Config.url_addAction+keyRandom);
		funAdd.setAccessTypeLevel(1);
		funAdd.setText("add");
		funAdd.setA2Menu2(menu2);
		menuFunctionDao.create(funAdd);
		//生成添加、修改的资源 page
		A2MenuFunction funAddPage = new A2MenuFunction();
		funAddPage.setUrl(Config.url_addeditPage+keyRandom);
		funAddPage.setAccessTypeLevel(1);
		funAddPage.setText("add");
		funAddPage.setA2Menu2(menu2);
		menuFunctionDao.create(funAddPage);
		//生成添加、修改的资源 page
		A2MenuFunction funRemoveAction = new A2MenuFunction();
		funRemoveAction.setUrl(Config.url_removeAction+keyRandom);
		funRemoveAction.setAccessTypeLevel(1);
		funRemoveAction.setText("add");
		funRemoveAction.setA2Menu2(menu2);
		menuFunctionDao.create(funRemoveAction);

		return result.success();
	}

*/

/*

	private Result template2menu4Chart1(long _templateTableId, long menu1Id, String pageType, String menu2Name) {
		Result result = new Result();
		//取模板表
		A1TemplateTable table = a1TemplateTableDao.fetch(_templateTableId);
		String tableNameShow = table.getTableNameShow();
		String keyRandom = table.getKeyRandom();
		//menu2Name
		menu2Name = StringUtils.isNull(menu2Name)?tableNameShow:menu2Name;
		
		Search search = new Search();
		search.addFilterEqual("text", menu2Name);
		search.addFilterEqual("deleteFlag", "0");
		int count = menu2Dao.count(search);
		if(count > 0){
			return result.fault("已存在二级菜单，可删除原菜单后继续："+menu2Name);
		}
		
		//取一级菜单
		A2Menu1 menu1 = menu1Dao.fetch(menu1Id);

		String menu2Url = "";
		String funUrl = "";
		if("chartPie".equals(pageType)){
			menu2Url = Config.url_chartPie;
			menu2Url = menu2Url.replace(":_keyRandom", keyRandom);
			funUrl = Config.url_chartPieAction;
			funUrl = funUrl.replace(":_keyRandom", keyRandom);
		}else{
			menu2Url = Config.url_chartMS;
			menu2Url = menu2Url.replace(":_keyRandom", keyRandom);
			menu2Url = menu2Url.replace(":_chartType", pageType);
			funUrl = Config.url_chartMSAction;
			funUrl = funUrl.replace(":_keyRandom", keyRandom);
		}

		//生成二级菜单
		A2Menu2 menu2 = new A2Menu2();
		menu2.setText(menu2Name);
		menu2.setA2Menu1(menu1);
		menu2.setUrl(menu2Url);
		menu2Dao.create(menu2);
		//生成功能资源
		A2MenuFunction fun = new A2MenuFunction();
		fun.setUrl(funUrl);
		fun.setAccessTypeLevel(0);
		fun.setText("query");
		fun.setA2Menu2(menu2);
		menuFunctionDao.create(fun);
		
		return result.success();
	}
	

	@Override
	public Result template2menu4ChartSingle(long _templateColumnId, long menu1Id, String pageType, String menu2Name, String columnName) {
		Result result = new Result();
		//取列
		A1TemplateColumn templateColumn = a1TemplateColumnDao.fetch(_templateColumnId);
		//取模板表
		A1TemplateTable table = templateColumn.getA1TemplateTable();//a1TemplateTableDao.fetch(_templateTableId);
		String tableNameShow = table.getTableNameShow();
		String keyRandom = table.getKeyRandom();
		//menu2Name
		menu2Name = StringUtils.isNull(menu2Name)?tableNameShow:menu2Name;
		
		Search search = new Search();
		search.addFilterEqual("text", menu2Name);
		search.addFilterEqual("deleteFlag", "0");
		int count = menu2Dao.count(search);
		if(count > 0){
			return result.fault("已存在二级菜单，可删除原菜单后继续："+menu2Name);
		}
		
		//取一级菜单
		A2Menu1 menu1 = menu1Dao.fetch(menu1Id);

		String menu2Url = "";
		String funUrl = "";
		menu2Url = Config.url_chartSingle;
		menu2Url = menu2Url.replace(":_keyRandom", keyRandom);
		menu2Url = menu2Url.replace(":_chartType", pageType);
		menu2Url = menu2Url.replace(":_chartColumn", columnName);
		funUrl = Config.url_chartSingleAction;
		funUrl = funUrl.replace(":_keyRandom", keyRandom);

		//生成二级菜单
		A2Menu2 menu2 = new A2Menu2();
		menu2.setText(menu2Name);
		menu2.setA2Menu1(menu1);
		menu2.setUrl(menu2Url);
		menu2Dao.create(menu2);
		//生成功能资源
		A2MenuFunction fun = new A2MenuFunction();
		fun.setUrl(funUrl);
		fun.setAccessTypeLevel(0);
		fun.setText("query");
		fun.setA2Menu2(menu2);
		menuFunctionDao.create(fun);
		
		return result.success();
	}
*/


	/**
	 * 查询option数据字典列表
	 * @param search
	 * @return
	 */
	@Override
	public List<A1TemplateColumnOptionDataDto> queryOptionDataListBySearch(Search search) {
		List<A1TemplateColumnOptionDataDto> dtoList = new ArrayList<>();
		List<A1TemplateColumnOptionData> entityList =  optionDataDao.search(search);

		DTOAssembler assembler = DTOAssembler.newAssembler(A1TemplateColumnOptionDataDto.class);
		assembler.assembleDtos(dtoList, entityList, null, null);

		return dtoList;
	}

}
